package com.ran.qrt;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.date.TimeInterval;
import cn.hutool.core.io.file.FileWriter;
import cn.hutool.core.io.resource.ResourceUtil;
import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.ReUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.setting.Setting;
import com.ran.qrt.config.SpringConfig;
import org.apache.logging.log4j.util.Strings;
import org.junit.jupiter.api.Test;
import org.springframework.test.context.junit.jupiter.SpringJUnitConfig;

import java.io.File;
import java.sql.SQLException;
import java.sql.SQLOutput;
import java.util.*;

@SpringJUnitConfig(value = SpringConfig.class)
public class TariffTest {
    private final String DIR = "C:\\Users\\Administrator\\Desktop\\";
    private final String excelPath = DIR + "完整数据.xlsx";

    @Test
    public void testExportCode() throws Exception {
        final TimeInterval timer = DateUtil.timer();
        exportCode();
        System.out.println("执行完成，耗时：" + timer.intervalSecond() + "秒");
    }

    @Test
    public void tariff() throws Exception {
        final TimeInterval timer = DateUtil.timer();

        // 0. 根据表结构创建 Tariff 实体类
        // genBean("Tariff", "tariff.txt");

        // 1. 创建test表
        createTest();

        // 2. 读取excel文件
        readExcel();

        // 3. 处理name1和code1
        processName1AndCode1();

        // 4. 关联旧表品目注释、子目注释、出口法定、出口暂定、反补贴、反倾销、申报要素
        processNote();

        // 5. 处理对美加征
        processAdd();

        // 6. 导出未匹配的税则号
        exportCode();

        System.out.println("执行完成，耗时：" + timer.intervalSecond() + "秒");
    }

    // 税号拆分(split)、新增(add)、删除(del)
    @Test
    public void codeChange() throws Exception {
        createTestBak();
        codeSplit();
        codeAdd();
        fieldAdd();
        Db.use().execute("update test_bak set zz_xh='3',zz='3' where code in ('30043900.21','30043900.22')");
        Db.use().execute("update test_bak set name=?,name1=? where code1=?", "双酚A型聚碳酸酯按重量计含量小于99%的其他聚碳酸酯", "双酚A型聚碳酸酯按重量计含量小于99%的其他聚碳酸酯", "3907400090");

        Entity entity = Db.use().queryOne("select * from test_bak where code=?", "90022010.90");
        final Integer id = entity.getInt("id");
        final Entity item = new Entity();
        item.setTableName("test_bak");
        item.set("id", id + 1);
        item.set("name", "--- 其他");
        item.set("name1", "其他");
        item.set("code", "9002.2090");
        item.set("code1", "90022090");
        Db.use().insert(item);

        genResult();
        Db.use().execute("update gnys a left join tariff1 b on a.code1=b.code1 set b.gnys=a.gnys where a.gnys!=b.gnys and b.id>0");
    }

    // 生成备份表
    private void createTestBak() throws SQLException {
        Db.use().execute("drop table if exists test_bak;");
        Db.use().execute("SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';");
        Db.use().execute("CREATE TABLE `test_bak` LIKE `test`;");
        Db.use().execute("INSERT INTO `test_bak` SELECT * FROM `test`;");
        Db.use().execute("ALTER TABLE `test_bak` ADD `ids` int(11) NOT NULL AFTER `id`;");
        Db.use().execute("UPDATE test_bak SET ids=id*100");
        Db.use().execute("ALTER TABLE `test_bak` DROP `id`;");
        Db.use().execute("ALTER TABLE `test_bak` CHANGE `ids` `id` int(11) NOT NULL FIRST;");

        // 新增字段
        Db.use().execute("ALTER TABLE `test_bak` ADD `yatai` varchar(20) COLLATE 'utf8mb4_general_ci' NOT NULL COMMENT '亚太特惠' AFTER `dm_mjl`");
        Db.use().execute("ALTER TABLE `test_bak` ADD `dongmeng_lao` varchar(20) COLLATE 'utf8mb4_general_ci' NOT NULL COMMENT '东盟特惠 (老)' AFTER `yatai`");
        Db.use().execute("ALTER TABLE `test_bak` ADD `dongmeng_jian` varchar(20) COLLATE 'utf8mb4_general_ci' NOT NULL COMMENT '东盟特惠 (柬)' AFTER `dongmeng_lao`");
        Db.use().execute("ALTER TABLE `test_bak` ADD `dongmeng_mian` varchar(20) COLLATE 'utf8mb4_general_ci' NOT NULL COMMENT '东盟特惠 (缅)' AFTER `dongmeng_jian`");
        Db.use().execute("ALTER TABLE `test_bak` ADD `recp_han` varchar(20) COLLATE 'utf8mb4_general_ci' NOT NULL COMMENT 'RCEP韩国' AFTER `dongmeng_mian`");
        Db.use().execute("ALTER TABLE `test_bak` ADD `recp_ao` varchar(20) COLLATE 'utf8mb4_general_ci' NOT NULL COMMENT 'RCEP澳大利亚' AFTER `recp_han`");
        Db.use().execute("ALTER TABLE `test_bak` ADD `recp_xin` varchar(20) COLLATE 'utf8mb4_general_ci' NOT NULL COMMENT 'RCEP新西兰' AFTER `recp_ao`");
        Db.use().execute("ALTER TABLE `test_bak` ADD `recp_dong` varchar(20) COLLATE 'utf8mb4_general_ci' NOT NULL COMMENT 'RCEP东盟' AFTER `recp_xin`");
    }

    // 税号拆分
    private void codeSplit() throws SQLException {
        Db.use().execute("delete from split where new_code='' and old_code=''");
        Db.use().execute("update split set zj_hg='' where zj_hg='-'");
        Db.use().execute("update split set zj_sj='' where zj_sj='-'");
        final List<Entity> list = Db.use().query("select * from split");
        Entity prev = null;
        int id = 0;
        for (Entity item : list) {
            final String oldCode = item.getStr("old_code");
            if (Strings.isNotBlank(oldCode)) {
                if (prev != null) {
                    processMainCode(prev);
                }
                prev = Db.use().queryOne("select * from test_bak where code=?", oldCode);
                if (prev == null) {
                    throw new RuntimeException("未查找数据：" + oldCode);
                }
                prev.set("code", item.getStr("new_code"));
                id = prev.getInt("id") + 1;
                continue;
            }
            assert prev != null;
            Entity clone = prev.clone();
            clone.remove("des");
            clone.set("id", id++);
            final String code = item.getStr("new_code");
            clone.set("name1", item.getStr("name"));
            clone.set("code", code);
            clone.set("code1", code.replace(".", ""));
            for (Map.Entry<String, Object> entry : item.entrySet()) {
                if (entry.getKey().contains("code") || entry.getKey().equals("id")) {
                    continue;
                }
                final Object value = entry.getValue();
                if (Strings.isNotBlank(value.toString())) {
                    clone.set(entry.getKey(), value);
                }
            }
            Db.use().insert(clone);
        }
        if (prev != null) {
            processMainCode(prev);
        }
    }

    // 税号新增
    private void codeAdd() throws SQLException {
        final List<Entity> oldCodes = Db.use().query("select new_code,old_code from `add` group by old_code");
        for (Entity entity : oldCodes) {
            final String oldCode = entity.getStr("old_code");
            final Entity ref = Db.use().queryOne("select * from test_bak where code=?", oldCode);
            if (ref == null) {
                throw new RuntimeException("未找到：" + oldCode);
            }
            final List<Entity> list = Db.use().query("select * from `add` where old_code=?", oldCode);
            int num = 1;
            for (Entity item : list) {
                final Entity clone = ref.clone();
                String code = item.getStr("new_code");
                final Number count = Db.use().queryNumber("select count(1) from test_bak where code=?", code);
                if (count.intValue() > 0) {
                    System.out.println("要增加的税号已存在：" + code);
                    continue;
                }
                String name = item.getStr("name");
                clone.set("name", name);
                clone.set("name1", name);
                clone.set("code", code);
                clone.set("code1", code.replace(".", ""));
                clone.set("id", clone.getInt("id") + num);
                num++;
                clone.set("eng_name", "");
                for (Map.Entry<String, Object> v : entity.entrySet()) {
                    final String key = v.getKey();
                    if (!key.contains("code")) {
                        clone.set(key, v.getValue());
                    }
                }
                Db.use().insert(clone);
            }
        }
    }

    // 更新新增协定字段
    private void fieldAdd() throws SQLException {
        String sql = "update tehui a left join test_bak b " +
                "on a.code1=left(b.code1,8) " +
                "set b.yatai=a.yatai," +
                "b.dongmeng_lao=a.dongmeng_lao," +
                "b.dongmeng_jian=a.dongmeng_jian," +
                "b.dongmeng_mian=a.dongmeng_mian," +
                "b.recp_han=a.recp_han," +
                "b.recp_ao=a.recp_ao," +
                "b.recp_xin=a.recp_xin," +
                "b.recp_dong=a.recp_dong " +
                "where length(b.code1)=10";
        final int num = Db.use().execute(sql);
        System.out.println("更新协定字段结果：" + num);
    }

    // 生成最终结果
    private void genResult() throws SQLException {
        Db.use().execute("drop table if exists tariff1");
        Db.use().execute("create table tariff1 like test_bak");
        Db.use().execute("insert into tariff1 select * from test_bak order by id");
        Db.use().execute("ALTER TABLE `tariff1` DROP id;");
        Db.use().execute("ALTER TABLE `tariff1` ADD `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;");
    }

    // 处理被拆分的税则
    private void processMainCode(Entity entity) throws SQLException {
        String code = entity.getStr("code");
        if ("".equals(code)) {
            // 删除
            Db.use().execute("delete from test_bak where id=?", entity.getInt("id"));
            return;
        }

        String code1 = entity.getStr("code1");
        code1 = code1.substring(0, 8);
        entity.set("code1", code1);
        entity.set("code", code1.substring(0, 4) + "." + code1.substring(4));
        entity.set("note_id", 0);
        entity.set("dump", 0);
        entity.set("sub", 0);
        String filter = ",id,code,code1,name,name1,des,eng_name,note_id,dump,sub,";
        for (Map.Entry<String, Object> item : entity.entrySet()) {
            String key = "," + item.getKey() + ",";
            if (!filter.contains(key)) {
                entity.set(item.getKey(), "");
            }
        }
        final Entity where = new Entity();
        where.set("id", entity.getInt("id"));
        Db.use().update(entity, where);
    }

    // 1. 创建test表
    private void createTest() throws Exception {
        String sql = "CREATE TABLE IF NOT EXISTS `test` (\n" +
                "  `id` int(11) NOT NULL AUTO_INCREMENT,\n" +
                "  `code` varchar(20) NOT NULL DEFAULT '' COMMENT '税号（商品编码）',\n" +
                "  `code1` varchar(20) NOT NULL DEFAULT '' COMMENT '税号（商品编码）1',\n" +
                "  `name` varchar(1000) NOT NULL DEFAULT '' COMMENT '货品名称',\n" +
                "  `name1` varchar(1000) NOT NULL DEFAULT '' COMMENT '货品名称',\n" +
                "  `note_id` int(11) NOT NULL DEFAULT '0' COMMENT '是否有品目子目注释，0-没有',\n" +
                "  `des` varchar(500) NOT NULL DEFAULT '' COMMENT '附加说明',\n" +
                "  `eng_name` varchar(1000) NOT NULL DEFAULT '' COMMENT '英文货品名称',\n" +
                "  `jk_zhg` varchar(100) NOT NULL DEFAULT '' COMMENT '最惠国税率%',\n" +
                "  `jk_zhg_des` varchar(1000) NOT NULL DEFAULT '' COMMENT '最惠国税率额外说明',\n" +
                "  `jk_zd` varchar(50) NOT NULL DEFAULT '' COMMENT '进口暂定税率%',\n" +
                "  `jk_pt` varchar(150) NOT NULL DEFAULT '' COMMENT '普通税率%',\n" +
                "  `ck_fd` varchar(50) NOT NULL DEFAULT '' COMMENT '出口法定税率%',\n" +
                "  `ck_zd` varchar(50) NOT NULL DEFAULT '' COMMENT '出口暂定税率%',\n" +
                "  `zz_xh` varchar(200) NOT NULL DEFAULT '' COMMENT '增值税/消费税',\n" +
                "  `zz` varchar(10) NOT NULL DEFAULT '' COMMENT '增值税%',\n" +
                "  `xh` varchar(200) NOT NULL DEFAULT '' COMMENT '消费税%',\n" +
                "  `ckds` varchar(10) NOT NULL DEFAULT '' COMMENT '出口退税率％',\n" +
                "  `tjdw1` varchar(20) NOT NULL DEFAULT '' COMMENT '统计第一单位',\n" +
                "  `tjdw2` varchar(20) NOT NULL DEFAULT '' COMMENT '统计第二单位',\n" +
                "  `zj_hg` varchar(20) NOT NULL DEFAULT '' COMMENT '海关进出口证件代码',\n" +
                "  `spbz` varchar(500) NOT NULL DEFAULT '' COMMENT '商品备注（CIQ扩展码）',\n" +
                "  `zj_sj` varchar(20) NOT NULL DEFAULT '' COMMENT '商检检验检疫类别',\n" +
                "  `qt_dm` varchar(20) NOT NULL DEFAULT '' COMMENT '东盟',\n" +
                "  `qt_ytwg` varchar(20) NOT NULL DEFAULT '' COMMENT '亚太',\n" +
                "  `qt_zl` varchar(20) NOT NULL DEFAULT '' COMMENT '智利',\n" +
                "  `qt_bjst` varchar(150) NOT NULL DEFAULT '' COMMENT '巴基斯坦',\n" +
                "  `qt_bd` varchar(20) NOT NULL DEFAULT '' COMMENT '冰岛',\n" +
                "  `qt_gsdl` varchar(20) NOT NULL DEFAULT '' COMMENT '哥斯达黎',\n" +
                "  `qt_bl` varchar(20) NOT NULL DEFAULT '' COMMENT '秘鲁',\n" +
                "  `qt_xxl` varchar(50) NOT NULL DEFAULT '' COMMENT '新西兰',\n" +
                "  `qt_rs` varchar(20) NOT NULL DEFAULT '' COMMENT '瑞士',\n" +
                "  `qt_xjp` varchar(20) NOT NULL DEFAULT '' COMMENT '新加坡',\n" +
                "  `qt_hg` varchar(100) NOT NULL DEFAULT '' COMMENT '韩国',\n" +
                "  `qt_adly` varchar(100) NOT NULL DEFAULT '' COMMENT '澳大利亚',\n" +
                "  `qt_gl` varchar(20) NOT NULL DEFAULT '' COMMENT '格鲁吉亚',\n" +
                "  `qt_mlqs` varchar(50) NOT NULL DEFAULT '' COMMENT '毛里求斯',\n" +
                "  `rcep` varchar(10) NOT NULL DEFAULT '' COMMENT 'RCEP组织',\n" +
                "  `qt_njlg` varchar(10) NOT NULL DEFAULT '' COMMENT '尼加拉瓜',\n" +
                "  `qt_xg_om_tw` varchar(20) NOT NULL DEFAULT '' COMMENT '香港、澳门/台湾',\n" +
                "  `qt_xg` varchar(20) NOT NULL DEFAULT '' COMMENT '香港',\n" +
                "  `qt_om` varchar(20) NOT NULL DEFAULT '' COMMENT '澳门',\n" +
                "  `qt_tw` varchar(20) NOT NULL DEFAULT '' COMMENT '台湾',\n" +
                "  `qt_jpz` varchar(20) NOT NULL DEFAULT '' COMMENT '柬埔寨',\n" +
                "  `dm_lg` varchar(20) NOT NULL DEFAULT '' COMMENT '老挝',\n" +
                "  `dm_mjl` varchar(20) NOT NULL DEFAULT '' COMMENT '孟加拉',\n" +
                "  `dm_3g` varchar(20) NOT NULL DEFAULT '' COMMENT '3国（目前只显示2国）',\n" +
                "  `dm_24g` varchar(20) NOT NULL DEFAULT '' COMMENT '42国（最新改为41国）',\n" +
                "  `gnys` varchar(1000) NOT NULL DEFAULT '' COMMENT '申报要素',\n" +
                "  `add` varchar(1000) NOT NULL DEFAULT '' COMMENT '对美加征',\n" +
                "  `dadd` varchar(1000) NOT NULL DEFAULT '' COMMENT '对美加征删除（目前不知道有什么用）',\n" +
                "  `gnys_dz` varchar(500) NOT NULL DEFAULT '' COMMENT '举例',\n" +
                "  `dump` tinyint(4) NOT NULL DEFAULT '0' COMMENT '反倾销',\n" +
                "  `sub` tinyint(4) NOT NULL DEFAULT '0' COMMENT '反补贴',\n" +
                "  PRIMARY KEY (`id`),\n" +
                "  KEY `code` (`code`),\n" +
                "  KEY `code1` (`code1`)\n" +
                ") ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;";
        int res = Db.use().execute(sql);
        if (res == 0) {
            System.out.println("1. test表已存在，不创建");
        } else {
            System.out.println("1. 创建test表");
        }
    }

    // 2. 读取excel文件
    private void readExcel() throws Exception {
        final Number num = Db.use().queryNumber("select count(1) from test");
        if (num.intValue() > 0) {
            System.out.println("2. excel表格已读取，跳过");
            return;
        }

        final ExcelReader reader = ExcelUtil.getReader(excelPath);
        final List<List<Object>> all = reader.read();
        final Setting setting = new Setting("config.setting");
        final Map<String, String> map = setting.getMap("tariff");
        final HashMap<Integer, String> fieldIndex = new HashMap<>();
        int index = 0;
        for (Object v : all.get(0)) {
            String s = (String) v;
            s = s.replaceAll(" ", "");
            for (Map.Entry<String, String> item : map.entrySet()) {
                if (s.contains(item.getValue())) {
                    fieldIndex.put(index, item.getKey());
                    break;
                }
            }
            index++;
        }
        if (fieldIndex.size() != map.size()) {
            Set<String> keys = map.keySet();
            for (String key : fieldIndex.values()) {
                keys.remove(key);
            }
            System.out.println("2. 以下值没匹配到，请检查：" + keys);
            throw new Exception("字段未匹配");
        }

        /*
        需要特殊处理的有：
        1、最惠国、亚太、瑞士，如果用/连接2个数字，按月份选择左边或右边数字，上半年左边，下半年右边
        2、增值/消费税，如果用/连接2个数字，左边增值，右边消费
        3、计量单位，要拆分成第1单位和第2单位
        4、港澳台，/左边为港澳，右边为台
        5、2国/42国，/左边为2国，右边为42国
         */
        boolean left = false;
        final int month = Calendar.getInstance().get(Calendar.MONTH);
        if (month < 2 || month > 10) {
            left = true;
        }

        for (int i = 1; i < all.size(); i++) {
            final List<Object> row = all.get(i);
            final Entity entity = Entity.create("test");
            String twoNum = "^[\\d.]+/[\\d.]+$";
            for (Map.Entry<Integer, String> item : fieldIndex.entrySet()) {
                String field = item.getValue();
                index = item.getKey();
                if (row.get(index) == null) {
                    continue;
                }
                String val = row.get(index).toString();

                val = Strings.trimToNull(val);
                if (Strings.isBlank(val)) {
                    continue;
                }

                String[] halfYearChangeFields = {"jk_zhg", "qt_ytwg", "qt_rs"};
                if (ArrayUtil.contains(halfYearChangeFields, field)) {
                    // 1、最惠国、亚太、瑞士，如果用/连接2个数字，按月份选择左边或右边数字，上半年左边，下半年右边
                    final boolean match = ReUtil.isMatch(twoNum, val);
                    if (match) {
                        final String[] arr = val.split("/");
                        entity.set(field, left ? arr[0] : arr[1]);
                    } else {
                        entity.set(field, val);
                    }
                } else if ("zz_xh".equals(field)) {
                    // 2、增值/消费税，如果用/连接2个数字，左边增值，右边消费
                    entity.set(field, val);
                    if (val.contains("/")) {
                        int splitIndex = val.indexOf("/");
                        entity.set("zz", val.substring(0, splitIndex));
                        entity.set("xh", val.substring(splitIndex + 1));
                    } else {
                        entity.set("zz", val);
                        entity.set("xh", 0);
                    }
                } else if ("tjdw1".equals(field)) {
                    // 3、计量单位，要拆分成第1单位和第2单位
                    final String[] arr = val.split("/");
                    entity.set("tjdw1", arr[0]);
                    if (arr.length > 1) {
                        entity.set("tjdw2", arr[1]);
                    }
                } else if ("qt_xg_om_tw".equals(field)) {
                    // 4、港澳台，／左边为港澳，右边为台
                    entity.set(field, val);
                    final String[] arr = val.split("/");
                    entity.set("qt_xg", arr[0]);
                    entity.set("qt_om", arr[0]);
                    if (arr.length > 1) {
                        entity.set("qt_tw", arr[1]);
                    }
                } else if ("dm_3g".equals(field)) {
                    // 5、2国/42国，／左边为2国，右边为42国
                    final String[] arr = val.split("/");
                    entity.set(field, val);
                    if (arr.length > 1) {
                        entity.set("dm_24g", arr[1]);
                    }
                } else {
                    entity.set(field, val);
                }
            }
            try {
                Db.use().insert(entity);
            } catch (Exception exp) {
                System.out.println("插入失败：" + exp.getMessage());
            }
        }
        System.out.println("2. excel表格插入完成");
    }

    // 3. 处理name1和code1
    private void processName1AndCode1() throws Exception {
        Number num = Db.use().queryNumber("select count(1) from test where code1!=''");
        if (num.intValue() > 0) {
            System.out.println("3. name1和code1处理完成");
            return;
        }
        Db.use().execute("update test set name1=replace(name,'-','')");
        Db.use().execute("update test set name1=replace(name1,' ','')");
        Db.use().execute("update test a left join test b on a.id+1=b.id and left(b.code,8) = replace(a.code,'.','') set a.code=concat(replace(a.code,'.',''),'.00') where length(a.code)=9 and b.id is null");
        Db.use().execute("update test set code1=replace(code,'.','')");
        do {
            num = Db.use().queryNumber("select count(1) from test where code1=''");
            if (num.intValue() == 0) {
                break;
            }
            Db.use().execute("update test a left join test b on a.id+1=b.id set a.code1=left(b.code1,5) where a.code1=''");
        } while (num.intValue() > 0);

        System.out.println("3. name1和code1处理完成");
    }

    // 4. 关联旧表品目注释、子目注释、出口法定、出口暂定、反补贴、反倾销、申报要素
    private void processNote() throws Exception {
        final Number num = Db.use().queryNumber("select count(1) from test where note_id>0");
        if (num.intValue() > 0) {
            System.out.println("4. 品目注释等旧表数据关联完成");
            return;
        }
        Db.use().execute("update test a left join tariff b on a.code=b.code set a.note_id=b.note_id,a.ck_fd=b.ck_fd,a.ck_zd=b.ck_zd,a.sub=b.sub,a.dump=b.dump,a.gnys=b.gnys where a.code!='' and b.id>0");
        System.out.println("4. 品目注释等旧表数据关联完成");
    }

    // 5. 处理对美加征（旧处理方法）
    private void processAdd() throws Exception {
        final Number num = Db.use().queryNumber("select count(1) from test where `add`!=''");
        if (num.intValue() > 0) {
            System.out.println("5. 对美加征处理完成");
            return;
        }
        Db.use().execute("update test a left join tariff b on a.code1=b.code1 set a.`add`=b.`add` where b.id>0");
        System.out.println("5. 对美加征处理完成");
    }

    // 5. 处理对美加征（旧处理方法）
    private void processAdd1() throws Exception {
        final Number num = Db.use().queryNumber("select count(1) from test where `add`!=''");
        if (num.intValue() > 0) {
            System.out.println("5. 对美加征处理完成");
            return;
        }
        final List<Entity> list = Db.use().query("select a.id,a.code,a.jk_zhg new_zhg,a.jk_zd new_zd,b.jk_zhg old_zhg,b.jk_zd old_zd,b.`add` old_add from test a left join tariff b on a.code1=b.code1 where b.`add`!=''");
        String isNumber = "^[-\\d.]+$";
        for (Entity entity : list) {
            final String oldAdd = entity.getStr("old_add");
            if (ReUtil.isMatch(isNumber, oldAdd)) {
                // 对美加征是数字
                float add = Float.parseFloat(entity.getStr("old_add"));
                String newZhgStr = entity.getStr("new_zhg");
                String oldZhgStr = entity.getStr("old_zhg");
                String newZdStr = entity.getStr("new_zd");
                String oldZdStr = entity.getStr("old_zd");
                if (!StrUtil.equals(newZhgStr, oldZhgStr) || !StrUtil.equals(newZdStr, oldZdStr)) {
                    float oldMin = 0;
                    float newMin = 0;
                    if (ReUtil.isMatch(isNumber, oldZhgStr) && ReUtil.isMatch(isNumber, oldZdStr)) {
                        oldMin = Math.min(entity.getFloat("old_zhg"), entity.getFloat("old_zd"));
                    }
                    if (ReUtil.isMatch(isNumber, newZhgStr) && ReUtil.isMatch(isNumber, newZdStr)) {
                        newMin = Math.min(entity.getFloat("new_zhg"), entity.getFloat("new_zd"));
                    }
                    add = add - oldMin + newMin;
                }
                Db.use().execute("update test set `add`=? where id=?", add, entity.getInt("id"));
            } else {
                // 对美加征是文字
                String add = "";
                if (ReUtil.isMatch(isNumber, entity.getStr("new_zhg"))) {
                    final String[] arr = entity.getStr("old_add").split("加");
                    if (arr.length < 2) {
                        throw new Exception("对美加征数据: " + entity);
                    }
                    float rate = Float.parseFloat(arr[1]);
                    final Float newZhg = entity.getFloat("new_zhg");
                    rate += newZhg;
                    add = arr[0] + "加" + rate;
                } else {
                    add = entity.getStr("old_add");
                }
                Db.use().execute("update test set `add`=? where id=?", add, entity.getInt("id"));
            }
        }
        System.out.println("5. 对美加征处理完成");
    }

    // 6. 导出对应不上的税则号
    private void exportCode() throws Exception {
        Db.use().execute("drop table if exists tmp;");
        Db.use().execute("create table tmp select id,code,code1,`add` from tariff;");
        Db.use().execute("alter table tmp add index code1(code1),CHANGE `id` `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST,ENGINE='MyISAM';");

        // 新表有旧表没有
        List<Entity> list = Db.use().query("select a.id,a.code from test a left join tmp b on a.code1=b.code1 where b.code1 is null");
        ExcelWriter writer = ExcelUtil.getWriter(DIR + "新表有旧表没有.xlsx");
        writer.addHeaderAlias("id", "ID");
        writer.addHeaderAlias("code", "税号");
        writer.write(list);
        writer.close();

        // 旧表有新表没有
        list = Db.use().query("select a.id,a.code from tmp a left join test b on a.code1=b.code1 where a.code!='' and b.code1 is null");
        writer = ExcelUtil.getWriter(DIR + "旧表有新表没有.xlsx");
        writer.addHeaderAlias("id", "ID");
        writer.addHeaderAlias("code", "税号");
        writer.write(list);
        writer.close();

        // 对症加征不同税号
        list = Db.use().query("select a.id,a.code,a.`add`,b.id old_id,b.code old_code,b.`add` old_add from test a left join tmp b on a.code1=b.code1 where b.code1!='' and a.`add`!=b.`add`");
        writer = ExcelUtil.getWriter(DIR + "对美加征差异.xlsx");
        writer.addHeaderAlias("id", "新表ID");
        writer.addHeaderAlias("code", "新表税号");
        writer.addHeaderAlias("add", "新表对美加征");
        writer.addHeaderAlias("old_id", "旧表ID");
        writer.addHeaderAlias("old_code", "旧表税号");
        writer.addHeaderAlias("old_add", "旧表对美加征");
        String isNumber = "^[.\\d-]+$";
        List<Entity> newList = list.stream().filter(item -> {
            String newAdd = item.getStr("add");
            String oldAdd = item.getStr("old_add");
            if (ReUtil.isMatch(isNumber, newAdd) && ReUtil.isMatch(isNumber, oldAdd)) {
                float diff = Math.abs(item.getFloat("add") - item.getFloat("old_add"));
                if (diff < 0.01) {
                    return false;
                }
            }
            return true;
        }).toList();
        writer.write(newList);
        writer.close();

        Db.use().execute("drop table tmp;");
        System.out.println("6. 导出税则号");
    }

    // 根据表结构生成实体类
    private void genBean(String name, String path) {
        name = StrUtil.toCamelCase(name);

        final StringBuilder sb = new StringBuilder();
        sb.append("package com.ran.qrt.bean;\n");
        sb.append("import lombok.Data;\n\n");
        sb.append("@Data\n");
        sb.append("public class ").append(name).append(" {\n");

        final String str = ResourceUtil.readUtf8Str(path);
        final String[] arr = str.split("\n");
        for (String s : arr) {
            final String[] item = s.split(" ");
            String key = item[0];
            String type = item[1];
            if (s.contains("COMMENT")) {
                String comment = item[item.length - 1];
                comment = comment.replaceAll("'", "").replaceAll(",", "");
                sb.append("    // ").append(comment).append("\n");
            }
            key = key.replaceAll("`", "");
            key = StrUtil.toCamelCase(key);
            if (type.contains("varchar")) {
                type = "String";
            } else {
                type = "Integer";
            }
            sb.append("    private ").append(type).append(" ").append(key).append(";\n\n");
        }
        sb.append("}\n");
        final File dir = new File("");
        String file = dir.getAbsolutePath() + "/src/main/java/com/ran/qrt/bean/" + name + ".java";
        final FileWriter fileWriter = new FileWriter(file);
        fileWriter.write(sb.toString());
        System.out.println("实体类 " + name + " 创建成功");
    }
}
